package com.atguigu.gmall2023sugar.mapper;

import com.atguigu.gmall2023sugar.bean.C3;
import com.atguigu.gmall2023sugar.bean.OrderAndRefund;
import com.atguigu.gmall2023sugar.bean.Province;
import com.atguigu.gmall2023sugar.bean.Tm;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface TradeMapper {
    
    @Select("select sum(order_amount) from dws_trade_sku_order_window partition(par${date})")
    BigDecimal gmv(String date);  // yyyy-MM-dd  yyyyMMdd 20230718
    
    @Select("select trademark_name, " +
        "sum(order_amount) order_amount " +
        "from dws_trade_sku_order_window partition(par${date}) " +
        "group by trademark_name " +
        "order by order_amount desc " +
        "limit ${top};")
    List<Tm> gmvByTm(@Param("date") String date, @Param("top") int top);
    
    @Select("select " +
        "category3_name, " +
        "sum(order_amount) order_amount " +
        "from dws_trade_sku_order_window partition(par${date}) " +
        "group by category3_name " +
        "order by order_amount desc " +
        "limit ${top};")
    List<C3> gmvByC3(@Param("date") String date, @Param("top") int top);
    
    @Select("select " +
        "province_name, " +
        "sum(order_amount) order_amount, " +
        "sum(order_count) order_count " +
        "from dws_trade_province_order_window partition(par${date}) " +
        "group by province_name;")
    List<Province> province(@Param("date") String date);
    
    @Select("select \n" +
        "    if(a.trademark_name is not null, a.trademark_name, b.trademark_name) trademark_name,\n" +
        "    if(a.order_amount is not null, a.order_amount, 0) order_amount,\n" +
        "    if(b.refund_count is not null, b.refund_count, 0) refund_count,\n" +
        "    if(b.refund_user_count is not null, b.refund_user_count, 0) refund_user_count\n" +
        "from \n" +
        "(select \n" +
        "    trademark_name, \n" +
        "    sum(order_amount) order_amount \n" +
        "from dws_trade_sku_order_window partition(par${date}) \n" +
        "group by trademark_name) a\n" +
        "full join\n" +
        "(select \n" +
        "    trademark_name, \n" +
        "    sum(refund_count) refund_count, \n" +
        "    count(distinct(user_id)) refund_user_count\n" +
        "from dws_trade_trademark_category_user_refund_window partition(par${date}) \n" +
        "group by trademark_name) b\n" +
        "on a.trademark_name=b.trademark_name")
    List<OrderAndRefund> orderAndRefund(@Param("date") String date);
    
    
    
    
}
